/**
 * 
 */
package utl.mofen.delphi.cds;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import utl.mofen.intf.IDBOperator;
import utl.mofen.utils.GsonUtil;

import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;

/**
 * 解包Delphi打包好的数据成SQL语句
 * @author mofen
 * 
 */
public class DeltaUnPackage {

	//delphi传入的Bolb类型
	private static int TYPE_BOLB = 15;
	
	//数据操作接口
	private IDBOperator dbOperator = null;
	
	//打包的数据
	private JsonObject packJson = null;
	
	private String sqlHead = "";
	
	/**
	 * 
	 * @return 返回解析好注释信息
	 */
	public String getSqlHead()
	{
		return sqlHead;
	}
	
	//SQL语句
	private ArrayList<String> sqlList = new ArrayList<String>();
	
	/**
	 * 获取解析好的SQL语句,在执行Execute后获取
	 * @return
	 * 解析好的SQL语句
	 */
	public ArrayList<String> getSqlList()
	{
		return sqlList;
	}
	
	//表中新的字段名
	private ArrayList<String> fields = new ArrayList<String>();
	
	//与传入数据对应的需要更新的字段,如果不需要更新的字段为<空>
	private ArrayList<String> updateFields = new ArrayList<String>();
	
	//字段类型
	private ArrayList<Integer> fieldsType = new ArrayList<Integer>();
	
	private ArrayList<String> keyFieldList = new ArrayList<String>(); 
	
	//要更新的表名,解析得到
	private String table = null;
	
	//表主键
	private String keyFields = null;
	
	//安全更新(if Exists() Update else insert
	private Boolean safeUpdate = false;
	
	/**
	 * 处理头信息
	 * @throws Exception
	 */
	public void processHead() throws Exception
	{
		if (packJson.has("head"))
		{
		  JsonObject head = packJson.get("head").getAsJsonObject();
		  
		  //表名
		  if (head.has("table")) table = head.get("table").getAsString();
		  
		  //主键
		  if (head.has("keyfields"))
		  {
			 setKeyFields(head.get("keyfields").getAsString());			
		  }
		  
		  //安全更新(if Exists() Update else insert
		  if (head.has("safeUpdate")) safeUpdate = head.get("safeUpdate").getAsBoolean();		  
		}
		
		if (fields==null || fields.size()==0)
		{
			setTableFields(dbOperator.getTableFields(table));
		}
		
		processUpdateFields();

		
		sqlHead = "/*** \n" +
				  " * 更新数据\n"+
			      " * 表名:"+ table + "\n" +
			      " * 主键:"+ keyFields + "\n" + 
			      "*/";
	}
	
	/**
	 * 处理需要更新字段
	 */
	private void processUpdateFields()
	{
		if (packJson.has("fields"))
		{
			JsonArray tmp = packJson.get("fields").getAsJsonObject().get("names").getAsJsonArray();
			updateFields.clear();
			
			for (int i=0;i<tmp.size();i++)
			{
				if (fields.indexOf(tmp.get(i).getAsString()) == -1)
				{
					updateFields.add("");
				}else
				{
					updateFields.add(tmp.get(i).getAsString());
				}
			}	
			
			tmp = packJson.get("fields").getAsJsonObject().get("types").getAsJsonArray();
			this.fieldsType.clear();
			
			for (int i=0;i<tmp.size();i++)
			{
				fieldsType.add(tmp.get(i).getAsInt());
			}	
		}		
	}
	
	/**
	 * 
	 * @throws Exception
	 */
	public void execute() throws Exception
	{		
		//处理头信息
		processHead();
		
		//解包记录
		unPackRecs(packJson.get("recs"));
	}
	
	/**
	 * 打印出SQL语句
	 */
	public void printSQL()
	{	
		for (int i=0;i<sqlList.size();i++)
		{
			System.out.println(sqlList.get(i));
		}
	}
	
	public String getUpdateSQL()
	{
		StringBuilder sb = new StringBuilder();
		for (int i=0;i<sqlList.size();i++)
		{
			sb.append(sqlList.get(i));
		}
		return sb.toString();
	}
	
	
	/**
	 * 执行解析好的SQL
	 * @param withTrans
	 *    是否在开启事务执行
	 * @throws SQLException 
	 */
	public void ExecuteUpdateSQL(Boolean withTrans) throws SQLException
	{				
		if (withTrans) dbOperator.beginTrans("DeltaUnPack");
		
		try
		{
			for (int i=0;i<sqlList.size();i++)
			{
				dbOperator.executeSQL(sqlList.get(i));
			}
			if (withTrans) dbOperator.commitTrans("DeltaUnPack");
		}catch(Exception e)
		{
			if (withTrans) dbOperator.rollBack("DeltaUnPack");
		}
		
	}
	
	/**
	 * 解析更新数据	
	 * @param recs
	 *    数据数组
	 [
	    {修改数据},{插入数据},{删除数据}
	 ]
	 */
	public void unPackRecs(JsonElement recs)
	{
		JsonArray recData = recs.getAsJsonArray();
		sqlList.clear();		
		
		for (int i=0;i<recData.size();i++)
		{
			JsonObject rec = recData.get(i).getAsJsonObject();
			String sql = "";
			switch (rec.get("_st").getAsInt())
			{
			  case DeltaConstant.REC_TYPE_Insert:
				  sql = unPackInsertRec(rec.get("data"));
				  break;
			  case DeltaConstant.REC_TYPE_Modified:
				  sql =  this.unPackModifiedRec(rec);
				  break;
			  case DeltaConstant.REC_TYPE_Delete:
				  sql =  this.unPackDeleteRec(rec);
				  break;
			}
			sqlList.add(sql);
		}
	}
	
	/**
	 * 设置表要更新的字段名
	 * 如果没有进行设置从packJson中读取
	 * @param s
	 */
	public void setTableFields(String s)
	{
		String[] sAr= s.split(",");		
		fields.clear();
		for (int i=0 ;i<sAr.length; i++)
		{
			fields.add(sAr[i]);			
		}		
	}
	
	/**
	 * 设置表的主键,
	 * 如果没有设置重packJson中读取
	 * @param s 可以多个字段中间已","隔开。
	 */	
	public void setKeyFields(String s)
	{
		keyFields = s;
		keyFieldList.clear();
		String[] sAr= keyFields.split(",");
		for (int i=0 ;i<sAr.length; i++)
		{
			keyFieldList.add(sAr[i]);			
		}
	}
	
	/**
	 * 获取SQL条件,可以多个主键
	 * @param Json数组 ["'545BD914-D882-4744-9A34-9679873147EC'"]
	 * @return FKey='545BD914-D882-4744-9A34-9679873147EC'
	 */
	public String getWhereString(JsonElement keyValues)
	{
		String s = "";
		
		for (int i=0; i<keyValues.getAsJsonArray().size(); i++)		
		{
			s = s + " AND " + keyFieldList.get(i) + "=" + keyValues.getAsJsonArray().get(i).getAsString();
		}
		
		if (s.length()>0)
			s = s.substring(5);
		
		return s;
	}
	
	/**
	 * 解析删除记录
	 * @param rec
	 * @return 返回SQL
	 */
	public String unPackDeleteRec(JsonElement rec)
	{
		String s = "";
		String where = getWhereString(rec.getAsJsonObject().get("key"));
		s = "DELETE FROM " + table + " WHERE " + where;
		return s;
	}
	
	/**
	 * 解析修改的记录
	 * @param 
      {
         "old": [和字段对应的数组]  //safeUpdate时用到
         "new": //修改的数据,key为字段序号
         {
            "1": "'x'"
         },
         "key": [与主键对应的数组],         
      },
	 * @return
	 */	
	public String unPackModifiedRec(JsonElement rec)
	{
		String s = "";
		JsonObject recData = rec.getAsJsonObject();
		String where = getWhereString(recData.get("key"));

		
		Set<Map.Entry<String, JsonElement>> tmp = recData.get("new").getAsJsonObject().entrySet();
		Iterator<Entry<String, JsonElement>> value_it = tmp.iterator();
		
		String upSet = "";
		while(value_it.hasNext())
		{
			Entry<String, JsonElement> v = value_it.next();
	        int i = Integer.parseInt(v.getKey());
	        
	        String f= updateFields.get(i);
	        if (!f.equals(""))
	        {	        	
				String value = v.getValue().getAsString();
	 		    if (!value.equals("NULL")) // NULL值不进行更新
				{
					if (this.fieldsType.get(i)==TYPE_BOLB)
					{
						value = "0x" + value;
					}
				}
	            upSet = upSet + "," + f + '=' + value;
	        }
		}
		
		if (upSet!="")
		{
			upSet = upSet.substring(1);
		    String upSql = "UPDATE "  + table + "\n" +
		    	           "    SET " + upSet + "\n" +
		    	           "    WHERE " + where;
		    if (safeUpdate)
		    {
	    	    s = "IF EXISTS(SELECT 1 FROM " + table + " WHERE " + where +")\n"  +
		            "  " + upSql + "\n" +
	    	    	"ELSE\n" + 
		            "  " + unPackInsertRec(recData.get("old")) + "";
		    }else
		    {
		    	s = upSql;		    
		    }
		}		
		return s;
	}
	
	/**
	 * 解析插入的记录
	 * @param Json数组(data部分与主键对应数组)
      {
         "_st": 2,
         "data": [
            "'DEA5ED58-2DE7-4E96-8BB4-0F1A202E6196'","NULL","'06'","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL","NULL"]
      },
	 * @return
	 */
	public String unPackInsertRec(JsonElement rec)
	{		
		String insFields = "";
		String insValues = "";
		
		JsonArray recData = rec.getAsJsonArray();
		
		for (int i=0;i<updateFields.size();i++)
		{
			if (updateFields.get(i) != "") 
			{
				String value = recData.get(i).getAsString();
				if (!value.equals("NULL")) // NULL值不进行更新
				{
					if (this.fieldsType.get(i)==TYPE_BOLB)
					{
						value = "0x" + value;
					}
					insFields = insFields + "," + updateFields.get(i);
					insValues = insValues + "," + value;
				}
			}
		}
		if (insFields!="")
		{
			insFields = insFields.substring(1);
			insValues = insValues.substring(1);
		}
		String s = "INSERT INTO " + table + "(" + insFields + ")\n" +
			       "       VALUES(" + insValues + ")";
		return s;
	}
	
	
	
	public void setDBOperator(IDBOperator dbOperator)
	{
		this.dbOperator = dbOperator;		
	}
	
	public void setPackJson(JsonObject data)
	{
		this.packJson = data;		
	}
	
	public void setPackString(String data)
	{
		this.packJson = GsonUtil.parse(data).getAsJsonObject();
	}
}
